# -*- coding: utf-8 -*-

"""
Datetime: 2020/03/18
Author: Zhang Yafei
Description: 
"""
import json
import time
import traceback
from datetime import datetime

import requests
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from settings import DATABASE
from DBHelper import db

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.75 Safari/537.36'}

# with open('/root/web/COVID-19/country_name_dict.json') as f:
with open('country_name_dict.json') as f:
    country_name_dict = json.load(f)


class TencentData():
    def __init__(self, conn_db):
        self.conn_db = conn_db
        self.session = requests.session()
        self.session.headers = headers
        self.china_url = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_other"
        self.city_url = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"
        self.foreign_url = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_foreign'
        self.global_history_url = "https://api.inews.qq.com/newsqa/v1/automation/modules/list?modules=FAutoGlobalStatis,FAutoGlobalDailyList"

    def get_covid_global_data(self):
        response = self.session.get(self.global_history_url).json()
        data = response['data']
        global_day_list = data['FAutoGlobalDailyList']
        global_statis = data['FAutoGlobalStatis']
        global_history = {}
        for global_day in global_day_list:
            date = f"2020-{global_day['date'].replace('.', '-')}"
            confirm = int(global_day['all']['confirm'])
            dead = int(global_day['all']['dead'])
            heal = int(global_day['all']['heal'])
            confirm_add = int(global_day['all']['newAddConfirm'])
            dead_rate = round(dead / confirm, 4) if confirm > 0 else 0.00
            heal_rate = round(heal / confirm, 4) if confirm > 0 else 0.00
            global_history[date] = {'confirm': confirm, "dead": dead, "heal": heal, 'confirm_add': confirm_add,
                                    "dead_rate": dead_rate, "heal_rate": heal_rate}

        global_statis_list = [global_statis[key] for key in global_statis]
        return global_history, global_statis_list

    def get_covid_foreign_data(self):
        response = self.session.get(self.foreign_url).json()
        data = json.loads(response['data'])
        foreign_list = data['foreignList']
        global_lasted = []

        for foreign in foreign_list:
            country = foreign['name']
            country_english_name = country_name_dict.get(country, '')
            confirm = int(foreign['confirm'])
            confirm_add = int(foreign['confirmAdd'])
            confirm_now = int(foreign['nowConfirm'])
            suspect = int(foreign['suspect'])
            dead = int(foreign['dead'])
            heal = int(foreign['heal'])
            dead_add = int(foreign['deadCompare'])
            heal_add = int(foreign['healCompare'])
            dead_rate = round(dead / confirm, 4) if confirm > 0 else 0.00
            heal_rate = round(heal / confirm, 4) if confirm > 0 else 0.00
            date = f"2020-{foreign['date'].replace('.', '-')}"
            global_lasted.append((date, country, country_english_name, confirm, confirm_now, suspect,
                                  dead, heal, confirm_add, dead_add, heal_add,
                                  dead_rate, heal_rate))

        return global_lasted

    def get_covid_china_data(self):
        response = self.session.get(self.china_url).json()
        data = json.loads(response['data'])
        china_day_list = data['chinaDayList']
        china_history = {}
        for china_day in china_day_list:
            confirm = china_day['confirm']
            suspect = china_day['suspect']
            dead = china_day['dead']
            heal = china_day['heal']
            overseas_inputs = china_day['importedCase']
            deadRate = china_day['deadRate']
            healRate = china_day['healRate']
            date = f"2020-{china_day['date'].replace('.', '-')}"
            china_history[date] = {'confirm': confirm, "suspect": suspect, "dead": dead, "heal": heal,
                                   'overseas': overseas_inputs, "deadRate": deadRate, "healRate": healRate, }
        china_day_add_list = data['chinaDayAddList']
        for day_add in china_day_add_list:
            confirm = day_add['confirm']
            suspect = day_add['suspect']
            dead = day_add['dead']
            heal = day_add['heal']
            overseas_inputs = day_add['importedCase']
            date = f"2020-{day_add['date'].replace('.', '-')}"
            china_history[date].update({"confirm_add": confirm, "suspect_add": suspect, "dead_add": dead,
                                        "heal_add": heal, "overseas_add": overseas_inputs})
        return china_history

    def get_covid_city_data(self):
        response = self.session.get(self.city_url)
        data = json.loads(response.text)['data']
        data = json.loads(data)
        last_update_time = data['lastUpdateTime']
        world_data = data['areaTree']
        china_data = world_data[0]['children']
        # world_data = world_data[1:]
        china_city = []
        for province in china_data:
            province_name = province['name']
            for city in province['children']:
                country = "中国"
                city_name = city['name']
                confirm = int(city['total']['confirm'])
                confirm_add = int(city['today']['confirm'])
                suspect = int(city['total']['suspect'])
                dead = int(city['total']['dead'])
                heal = int(city['total']['heal'])
                dead_rate = round(dead / confirm, 4) if confirm > 0 else 0.00
                heal_rate = round(heal / confirm, 4) if confirm > 0 else 0.00
                china_city.append(
                    [last_update_time, country, province_name, city_name, confirm, confirm_add, suspect, dead, heal,
                     dead_rate,
                     heal_rate])
        # world_country = []
        # for country in world_data:
        #     country_name = country['name']
        #     confirm = country['total']['confirm']
        #     confirm_add = country['today']['confirm']
        #     suspect = country['total']['suspect']
        #     dead = country['total']['dead']
        #     heal = country['total']['heal']
        #     # dead_rate = country['total']['deadRate']
        #     # heal_rate = country['total']['healRate']
        #     dead_rate = round(dead / confirm, 4) if confirm > 0 else 0.00
        #     heal_rate = round(heal / confirm, 4) if confirm > 0 else 0.00
        #     world_country.append([last_update_time, country_name, confirm, confirm_add, suspect,
        #                           dead, heal, dead_rate, heal_rate])
        return china_city

    def update_data(self):
        """ 更新china_history, world_hiostory, china_lasted和world_lasted表 """
        try:
            china_history = self.get_covid_china_data()
            china_lasted = self.get_covid_city_data()
            world_history, world_statis = self.get_covid_global_data()
            world_lasted = self.get_covid_foreign_data()
            print(f'{datetime.now()} 获取数据完成 正在更新数据库')
            china_history_sql = """
                    insert into china_history(date, confirm, suspect, dead, heal, overseas_inputs, dead_rate, heal_rate,confirm_add,suspect_add,dead_add,heal_add,overseas_inputs_add)
                    values (?,?,?,?,?,?,?,?,?,?,?,?,?)
                    """
            china_history_query = "select confirm from china_history where date=?"
            world_history_sql = """
                    insert into world_history(date, confirm, dead, heal, confirm_add, dead_rate, heal_rate)
                    values (?,?,?,?,?,?,?)
                  """
            world_history_query = "select confirm from world_history where date=?"
            world_lasted_sql = """
                     replace into world_lasted(date, country, country_english_name, confirm, confirm_now, suspect, dead, heal, confirm_add, dead_add, heal_add, dead_rate, heal_rate)
                     values (?,?,?,?,?,?,?,?,?,?,?,?,?)
                   """
            world_statis_sql = """
                replace into world_statis(now_confirm, confirm, heal, dead, 
                now_confirm_add, confirm_add, heal_add, dead_add, update_time) values (?,?,?,?,?,?,?,?,?)
            """
            world_statis_query = f"SELECT confirm={world_statis[1]} FROM world_statis"
            china_lasted_sql = "replace into china_lasted (update_time, country, province, city, confirm, confirm_add, suspect, dead, heal, dead_rate, heal_rate) values (?,?,?,?,?,?,?,?,?,?,?)"
            china_lasted_query = f"select '{china_lasted[0][0]}'=(select update_time from china_lasted order by id desc limit 1)"

            if self.conn_db == 'mysql':
                china_history_sql = china_history_sql.replace('?', '%s')
                china_history_query = china_history_query.replace('?', '%s')
                world_history_sql = world_history_sql.replace('?', '%s')
                world_history_query = world_history_query.replace('?', '%s')
                china_lasted_sql = china_lasted_sql.replace('?', '%s')
                world_lasted_sql = world_lasted_sql.replace('?', '%s')
                world_statis_sql = world_statis_sql.replace('?', '%s')

            # 更新china_history表
            china_history_update_bool = False
            params_list = []
            for date in china_history:
                data = china_history.get(date)
                if not db.fetchone(china_history_query, (date,)):
                    params = (date, data['confirm'], data['suspect'], data['dead'], data['heal'],
                              data['overseas'], data['deadRate'], data['healRate'], data.get('confirm_add'),
                              data.get('suspect_add'), data.get('dead_add'), data.get('heal_add'),
                              data.get('overseas_add'))
                    params_list.append(params)
                    if not china_history_update_bool:
                        china_history_update_bool = True
            db.execute_many(china_history_sql, params_list)
            if china_history_update_bool:
                print("china history 更新完毕")
            else:
                print("china history 已经是最新数据 暂时无需更新")

            # 更新world_history表
            world_history_update_bool = False
            today = time.strftime('%Y-%m-%d')
            for date in world_history:
                if date == today or world_history[date]['confirm_add'] == 0:
                    continue
                data = world_history.get(date)
                if not db.fetchone(world_history_query, (date,)):
                    params = (date, data['confirm'], data['dead'], data['heal'],
                              data.get('confirm_add'), data['dead_rate'], data['heal_rate'])
                    db.execute(world_history_sql, params)
                    if not world_history_update_bool:
                        world_history_update_bool = True
            if world_history_update_bool:
                print("world history 更新完毕")
            else:
                print("world history 已经是最新数据 暂时无需更新")

            # 更新world_lasted表
            db.execute_many(world_lasted_sql, world_lasted)
            print('world lasted 更新完毕')

            # 更新world_statis表
            if db.fetchone(world_statis_query)[0] == 0:
                delete_sql = f"delete from world_statis where update_time != '{world_statis[-1]}'"
                db.execute(world_statis_sql, world_statis)
                db.execute(delete_sql) 
                print('world statis 更新完毕')
            else:
                print('world statis 已经是最新数据 暂时无需更新')

            # 更新china_lasted表
            if not db.fetchone(china_lasted_query)[0]:
                db.execute_many(china_lasted_sql, china_lasted)
                print('china lasted 更新完毕')
            else:
                print("china lasted 已经是最新数据 暂时无需更新")
        except Exception:
            traceback.print_exc()


def get_baidu_hot():
    chrome_options = Options()
    chrome_options.add_argument("--headless")
    chrome_options.add_argument('--no-sandbox')

    driver = webdriver.Chrome(options=chrome_options, executable_path="chromedriver")
    # driver = webdriver.Chrome(options=chrome_options, executable_path="/root/web/COVID-19/chromedriver")
    url = "https://voice.baidu.com/act/virussearch/virussearch/?from=osari_map&tab=0"
    driver.get(url)
    driver.find_element_by_css_selector(".VirusHot_1-5-5_1Fqxy-").click()
    res_list = driver.find_elements_by_xpath('//*[@id="ptab-0"]/div/div[2]/section/a/div/span[2]')
    content = [res.text for res in res_list]
    driver.close()
    return content


def update_hotsearch(conn_db):
    """ 更新百度热搜 """
    try:
        context = get_baidu_hot()
        time_now = datetime.now().strftime("%Y-%m-%d %X")
        sql = "insert into hotsearch(update_time, content) values(?,?)"
        delete_sql = f"delete from hotsearch where update_time != '{time_now}'"
        if conn_db == 'mysql':
            sql = sql.replace('?', '%s')
        params_list = [(time_now, text) for text in context]
        db.execute_many(sql, params_list)
        db.execute(delete_sql)
        print("hotsearch\t数据更新完毕")
    except Exception:
        traceback.print_exc()


def run(conn_db):
    if conn_db in {'sqlite3', 'mysql'}:
        tencent = TencentData(conn_db)
        tencent.update_data()
        update_hotsearch(conn_db)
        print(f'{datetime.now()} 所有数据更新完毕')
    else:
        raise Exception("conn_db 只能是sqlite3 OR mysql")


if __name__ == '__main__':
    run(conn_db=DATABASE.get('use_db'))
    

# * 7-22/1 * * * python /root/web/covid-19/spider >> /root/web/log_spider 2>&1 &
